import javax.swing.JButton;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JLabel;
import javax.swing.ListSelectionModel;

import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;


public class UserPurchasesPanel extends JPanel {
	private static final long serialVersionUID = 6684464588122604175L;
	private final DatabaseInfo db_info;
	private JTable table;
	private JLabel revenue;
	private JLabel ticketNum;
	private String user;
	private JScrollPane scrolly;
	private ArrayList<PurchasesRow> purchase_list;

	/**
	 * Create the panel.
	 */
	public UserPurchasesPanel(DatabaseInfo the_db_info) {
		setLayout(null);
		user = the_db_info.getAccountID();
		db_info = the_db_info;

		JLabel lblTotalSpent = new JLabel("Total Spent:");
		lblTotalSpent.setFont(new Font("Tahoma", Font.PLAIN, 15));
		lblTotalSpent.setBounds(26, 327, 170, 19);
		add(lblTotalSpent);
		
		String rev = getQuery(
				"SELECT SUM(ticketPrice) " +
						"FROM purchases, tickets " +
						"WHERE purchases.cName = tickets.cName " +
						"AND purchases.ticketType = tickets.ticketType AND purchases.accountID ='"
						+ user + "';", true);
		if (rev.equals("null")) {
			rev = "0";
		}

		revenue = new JLabel("$" + rev);

		revenue.setFont(new Font("Tahoma", Font.PLAIN, 15));
		revenue.setBounds(115, 327, 133, 19);
		add(revenue);

		JLabel lblTotalTicketsBought = new JLabel("Total Tickets Bought:");
		lblTotalTicketsBought.setFont(new Font("Tahoma", Font.PLAIN, 15));
		lblTotalTicketsBought.setBounds(280, 327, 170, 19);
		add(lblTotalTicketsBought);

		ticketNum = new JLabel(getQuery(
				"SELECT COUNT(purchaseNum) " +
						"FROM purchases " +
						"WHERE accountID ='" + user + "';", true));

		ticketNum.setFont(new Font("Tahoma", Font.PLAIN, 15));
		ticketNum.setBounds(423, 327, 133, 19);
		add(ticketNum);

		JLabel lblPurchaseHistory = new JLabel("Purchase History");
		lblPurchaseHistory.setFont(new Font("Tahoma", Font.BOLD, 23));
		lblPurchaseHistory.setBounds(282, 24, 244, 38);
		add(lblPurchaseHistory);

		JButton btnRefresh = new JButton("Refresh");
		btnRefresh.setBounds(655, 330, 112, 23);
		add(btnRefresh);
		btnRefresh.addActionListener(new ActionListener(){
			@Override
			public void actionPerformed(ActionEvent arg0) {
				refreshTable();
				
				String rev = getQuery(
						"SELECT SUM(ticketPrice) " +
								"FROM purchases, tickets " +
								"WHERE purchases.cName = tickets.cName " +
								"AND purchases.ticketType = tickets.ticketType AND purchases.accountID ='"
								+ user + "';", true);
				if (rev.equals("null")) {
					rev = "0";
				}

				revenue.setText("$" + rev);
				ticketNum.setText(getQuery(
						"SELECT COUNT(purchaseNum) " +
								"FROM purchases " +
								"WHERE accountID ='" + user + "';", true));
			}
		});
		
		JLabel lblName = new JLabel("Name: ");
		lblName.setFont(new Font("Tahoma", Font.PLAIN, 11));
		lblName.setBounds(10, 28, 45, 14);
		add(lblName);
		
		JLabel name = new JLabel(getQuery("SELECT members.name FROM members WHERE members.accountID='"
				+ db_info.getAccountID() + "';", true));
		name.setFont(new Font("Tahoma", Font.BOLD, 11));
		name.setBounds(44, 28, 100, 14);
		add(name);
		
		JLabel lblFavoriteGenre = new JLabel("Favorite Genre:");
		lblFavoriteGenre.setFont(new Font("Tahoma", Font.PLAIN, 11));
		lblFavoriteGenre.setBounds(10, 48, 100, 14);
		add(lblFavoriteGenre);
		
		JLabel genre = new JLabel(getQuery("SELECT members.favGenre FROM members WHERE members.accountID='"
				+ db_info.getAccountID() + "';", true));
		genre.setFont(new Font("Tahoma", Font.BOLD, 11));
		genre.setBounds(89, 48, 150, 14);
		add(genre);
		
		createAddScrollPane(new JTable());
		refreshTable();

	}

	private ArrayList<String> splitRowStrings(String the_data) {
		ArrayList<String> ret = new ArrayList<String>();

		while(0 < the_data.length()) {
			int io = the_data.indexOf('\r');
			String s;
			if(0 > io)
				s = the_data.trim();
			else
				s = the_data.substring(0,io).trim();

			ret.add(s);

			if(0 > io)
				break;
			the_data = the_data.substring(io,the_data.length()).trim();
		}
		return ret;
	}

	private String[] splitString(String header_string) {
		header_string = header_string.trim();
		String[] temp = header_string.split("\t");

		int valid_count = 0;
		for(int i = 0 ; i < temp.length ; i++) {
			temp[i] = temp[i].trim();
			if(0 < temp[i].length())
				valid_count++;
		}

		String[] ret = new String[valid_count];
		int j = 0;
		for(int i = 0 ; i < temp.length ; i++) {
			if(0 < temp[i].length())
				ret[j++] = temp[i];
		}
		return ret;
	}

	private String convertTitle(final String str) {
		if(str.equals("purchaseNum")) return "Purchase ID";
		else if(str.equals("cName")) return "Cinema";
		else if(str.equals("title")) return "Movie";
		else if(str.equals("ticketType")) return "Ticket Type";
		else if(str.equals("accountID")) return "Account ID";
		else if(str.equals("ticketPrice")) return "Ticket Price";

		return str; //default
	}


	private String formQuery(boolean is_row_count) {
		String query = null;
		if (is_row_count) {
			query = "SELECT COUNT(purchaseNum) FROM purchases WHERE accountID ='"
					+ user + "';";
		} else {
			query = "SELECT purchases.purchaseNum, purchases.cName, purchases.title, purchases.ticketType, tickets.ticketPrice FROM purchases, tickets WHERE purchases.ticketType = tickets.ticketType AND purchases.cName = tickets.cName AND purchases.accountID ='"
					+ user + "';";
		}
		return query;
	}

	/**
	 * Executes a given query and returns the results;
	 * @param query The query to execute;
	 * @return The result of the query. Returns <code>null</code> if there
	 * 			was an exception thrown.
	 */
	private String getQuery(String query, boolean parse) {
		Db db = Db.getDb();
		String result = null;

		try {
			if (!parse)
				result = db.executeQuery(db_info.server, db_info.database, query, 
						db_info.user_name, db_info.password);
			else {
				result = db.executeQuery2(db_info.server, db_info.database, query, 
						db_info.user_name, db_info.password);
				result = result.replace("@", "");
			}

		} catch (Exception e1) {
			System.out.println(e1.getMessage());
			e1.printStackTrace();
		}

		return result;
	}
	
	private void createAddScrollPane(JTable table) {
		scrolly = new JScrollPane(table);
		scrolly.setBounds(10, 70, 761, 250);
		add(scrolly);
	}

	private void refreshTable() {
		final int column_count = 5;
		final int row_count;
		String rc = getQuery(formQuery(true), false);
		rc = rc.substring(rc.indexOf('\r'), rc.length()).trim();
		row_count = Integer.parseInt(rc);

		String result = getQuery(formQuery(false), false);

		if(null != result) {
			String header_line = result.substring(0, result.indexOf('\r'));

			String[] column_names = splitString(header_line);
			for(int i = 0 ; i < column_names.length ; i++)
				column_names[i] = convertTitle(column_names[i]);

			result = result.substring(result.indexOf('\r'),result.length()).trim();
			Object[][] table_data = new Object[row_count][column_count];
			purchase_list = new ArrayList<PurchasesRow>();

			int x = 0, y = 0;
			ArrayList<String> row_strings = splitRowStrings(result);
			
			PurchasesRow.is_cinema = false;
			for(String s : row_strings) {
				x = 0;
				PurchasesRow row = new PurchasesRow(s);
				purchase_list.add(row);
				table_data[y][x++] = row.getPurchaseNum();
				table_data[y][x++] = row.getCName();
				table_data[y][x++] = row.getTitle();
				table_data[y][x++] = row.getTicketType();
				table_data[y][x++] = row.getTicketPrice();
				y++;
			}
			PurchasesRow.is_cinema = true;


			table = new JTable(table_data, column_names);
			table.getColumnModel().getColumn(0).setPreferredWidth(72);
			table.getColumnModel().getColumn(1).setPreferredWidth(250);
			table.getColumnModel().getColumn(2).setPreferredWidth(220);
			table.getColumnModel().getColumn(3).setPreferredWidth(72);
			table.getColumnModel().getColumn(4).setPreferredWidth(72);
			table.setFillsViewportHeight(true);
			table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);


			this.remove(scrolly);
			createAddScrollPane(table);
		}
	}

}
